package huashu.net.complain.dao;

import huashu.net.complain.databean.IndicatorDTO;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import huashu.net.complain.entity.Indicators;

import com.sirdc.modules.core.dao.StringDao;

import java.util.List;

/**
 * 
 * @author xxq
 *
 */
@Repository
public class IndicatorsDao extends StringDao<Indicators> {

    //各年总数
    public IndicatorDTO getAllYearAmount(String area ) {
        StringBuffer sql = new StringBuffer();
        sql.append(" select sum(amount) sum ");
        sql.append(" from t_indicator");
        sql.append(" where 1=1 ");
        if(area!=null&&!"".equals(area))
        sql.append(" and area = '"+area+"'");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(IndicatorDTO.class));
        List<IndicatorDTO> dtos = (List<IndicatorDTO>) query.list();
        return dtos.get(0);
    }
    //历年数据量
    public  List<IndicatorDTO> getareaAmount()
    {
        StringBuffer sql  = new StringBuffer();
        sql.append("SELECT area ,SUM(amount) sum from t_indicator ");

        sql.append("GROUP BY area ");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(IndicatorDTO.class));
        List<IndicatorDTO> dtos = (List<IndicatorDTO>) query.list();
        return dtos;

    }

    //各个类别总数
    public  List<IndicatorDTO> getAllClassAmount()
    {
        StringBuffer sql  = new StringBuffer();
        sql.append(" select one_class_name name, sum(amount) sum  ");
        sql.append(" from t_indicator");
        sql.append(" where 1=1  ");
        sql.append("  and  avail='1' ");
        sql.append(" GROUP BY one_class_name");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(IndicatorDTO.class));
        List<IndicatorDTO> dtos = (List<IndicatorDTO>) query.list();
        return dtos;
    }

    //18类历年满意率
    public List<IndicatorDTO> getYearSatify(String classname ){
        StringBuffer sql = new StringBuffer();
        sql.append("select datetime, AVG(satifyrate) sum");
        sql.append(" from t_indicator ");
        sql.append(" where 1=1 ");
        if(classname!=null&&!"".equals(classname))
            sql.append(" and one_class_name ='"+classname+"'");
        sql.append("  and  avail='1' ");
        sql.append(" GROUP BY datetime ");

        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(IndicatorDTO.class));
        List<IndicatorDTO> dtos = (List<IndicatorDTO>) query.list();
        return dtos;
    }

    //历年批转率
    public List<IndicatorDTO> getYearEndorseRate(String classname)
    {
        StringBuffer sql  = new StringBuffer();
        sql.append(" select datetime, AVG(endorserate) sum ");
        sql.append(" from t_indicator");
        sql.append(" where 1=1 ");
        if(classname!=null&&!"".equals(classname))
            sql.append(" and one_class_name ='"+classname+"'");
        sql.append("  and  avail='1' ");
        sql.append(" GROUP BY datetime");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(IndicatorDTO.class));
        List<IndicatorDTO> dtos = (List<IndicatorDTO>) query.list();
        return dtos;
    }

    //历年回复率
    public List<IndicatorDTO> getreponseRate(String classname)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("select datetime, AVG(reponserate) sum ");
        sql.append("from t_indicator ");
        sql.append(" where 1=1 ");
        if(classname!=null&&!"".equals(classname))
            sql.append(" and one_class_name ='"+classname+"'");
        sql.append("  and  avail='1' ");
        sql.append("GROUP BY datetime ");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(IndicatorDTO.class));
        List<IndicatorDTO> dtos = (List<IndicatorDTO>) query.list();
        return dtos;
    }
    //各个区县详细数据
    public List<IndicatorDTO> getDetailInfo(String area)
    {
        StringBuffer sql = new StringBuffer();

       sql.append(" select area,datetime,one_class_name name, SUM(amount) sum,SUM(amount) finishsum, AVG(satifyrate) satifyrate, AVG(endorserate) endorserate , AVG(reponserate) reponserate ");
       sql.append(" from t_indicator");
       sql.append(" where 1=1 ");
       if (area!=null&&!"".equals(area))
           sql.append(" and area='"+area+"' ");
       sql.append(" GROUP BY datetime,area");
       SQLQuery query = getSession().createSQLQuery(sql.toString());
       query.setResultTransformer(Transformers.aliasToBean(IndicatorDTO.class));
       List<IndicatorDTO> dtos  = (List<IndicatorDTO>) query.list();
       return  dtos;
    }

    //18类 历年诉件量
    public List<IndicatorDTO> getYearAmount()
    {
        StringBuffer sql  = new StringBuffer();
       sql.append(" select datetime,sum(amount) sum  ");
       sql.append(" from t_indicator ");
       sql.append(" where 1=1 ");
        sql.append("  and  avail='1' ");
       sql.append(" GROUP BY datetime ");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(IndicatorDTO.class));
        List<IndicatorDTO> dtos  = (List<IndicatorDTO>) query.list();
        return  dtos;
    }

}
